package com.pine.dbant.common.plugin;


import com.google.common.collect.Lists;
import com.pine.dbant.common.exeception.ProviderException;
import com.pine.dbant.common.model.*;
import com.pine.dbant.common.plugin.impl.DefaultSqlSchemaStatVisitor;
import com.pine.dbant.common.plugin.meta.JdbcMetaAnalysisException;
import com.pine.sunflower.core.plugin.Configuration;
import com.pine.sunflower.core.utils.Strings;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.*;

/**
 * @author xiaoyuan
 * @create 2022/11/8 17:01
 **/
@Slf4j
public class JdbcConnector extends AbstractJdbcConnector {

    protected static final String COLUMN_NAME = "COLUMN_NAME";
    protected static final String DATA_TYPE = "DATA_TYPE";

    protected static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
    protected static final SimpleDateFormat DATE_TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    protected SqlSchemaStatVisitor sqlSchemaStatVisitor = new DefaultSqlSchemaStatVisitor();

    public JdbcConnector(Configuration configuration, Connection connection, ConnectionInfo connectionInfo) {
        super(configuration, connection, connectionInfo);
    }


    @Override
    public ExecuteRecord doExecute(SQLStatement statement, ExecuteParams params) throws ProviderException {
        if (StringUtils.isBlank(statement.getSql())) {
            return null;
        }
        ExecuteRecord executeRecord = new ExecuteRecord();
        executeRecord.setSql(params.getScript());
        executeRecord.setSuccess(true);
        try( PreparedStatement preparedStatement = connection.prepareStatement(params.getScript())) {
            preparedStatement.execute();
            int updateCount = preparedStatement.getUpdateCount();
            String message = "执行完毕";
            if (statement.getType().equals(SqlCommandType.UPDATE.name()) ||
                    statement.getType().equals(SqlCommandType.DELETE.name())) {
                message = Strings.format("影响条数：{}", updateCount);
            }
            executeRecord.setMessage(message);
        } catch (Exception e) {
            log.error("sql[{}]执行失败:{}", params.getScript(), e.getMessage());
            executeRecord.setSuccess(false);
            executeRecord.setMessage(e.getMessage());
        }
        return executeRecord;
    }

    @Override
    public QueryExecuteRecord doQuery(SQLStatement statement, QueryExecuteParams params) throws ProviderException {
        if (StringUtils.isBlank(statement.getSql())) {
            return null;
        }
        QueryExecuteRecord executeRecord = new QueryExecuteRecord();
        executeRecord.setSql(params.getScript());
        executeRecord.setSuccess(true);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<TableSource> tableSources = new ArrayList<>();
        try {
            String sql = params.getScript();
            if (statement.getType().equals(SqlCommandType.SELECT.name())) {
                sql = formatSql(params);
                tableSources = sqlSchemaStatVisitor.getColumnsOfTableSources(sql);
                tableSources.forEach(tableSource -> {
                    if (StringUtils.isBlank(tableSource.getCatalog())) {
                        tableSource.setCatalog(this.connectionInfo.getDbName());
                    }
                });
            } else {
                params.setNoPage(true);
                executeRecord.setEditAuth(false);
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setQueryTimeout(this.configuration.getInt(DbConstant.SQL_QUERY_TIMEOUT, DbConstant.DEFAULT_SQL_QUERY_TIMEOUT));
            resultSet = preparedStatement.executeQuery();
            List<Column> columns = getColumns(resultSet);
            if (statement.getType().equals(SqlCommandType.SELECT.name()) && tableSources.size() == 1) {
                TableSource tableSource = tableSources.get(0);
                if (StringUtils.isNotBlank(tableSource.getTableName()) && StringUtils.isNotBlank(tableSource.getCatalog())) {
                    List<PrimaryKey> primaryKeys = getPrimaryKeys(tableSource.getCatalog(), tableSource.getTableName());
                    if (CollectionUtils.isNotEmpty(primaryKeys)) {
                        executeRecord.setEditAuth(true);
                        primaryKeys.forEach(primaryKey -> {
                            Column column1 = columns.stream().filter(column -> column.getField().equalsIgnoreCase(primaryKey.getColumnName())).findFirst().orElse(null);
                            if (column1 == null) {
                                executeRecord.setEditAuth(false);
                            }
                            column1.setPrimaryKey(true);
                        });
                        if(executeRecord.isEditAuth()){
                            executeRecord.setTableName(tableSource.getFullName());
                        }
                    }
                }
            }
            List<Map<String, Object>> data = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(columns)) {
                data = getData(columns, resultSet);
            }
            int rowCount = 0;
            if (!statement.getType().equals(SqlCommandType.SELECT.name())) {
                rowCount = data.size();
            } else if (data.size() < params.getPageSize() && params.getPageIndex() == 1) {
                rowCount = data.size();
            } else {
                rowCount = executeQueryForCount(params.getScript());
            }

            executeRecord.setColumns(columns);
            executeRecord.setTotal((long) rowCount);
            executeRecord.setRows(data);
            executeRecord.setPageSize(params.getPageSize());
            executeRecord.setPageIndex(params.getPageIndex());
            executeRecord.setPageIndex(params.getPageIndex());
            executeRecord.setMessage("查询完成");
            executeRecord.setNoPage(params.isNoPage());
        } catch (Exception e) {
            log.error("sql[{}]执行失败:{}", params.getScript(), e.getMessage());
            executeRecord.setSuccess(false);
            executeRecord.setMessage(e.getMessage());
        }finally {
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {

                }
            }
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {

                }
            }
        }
        return executeRecord;
    }

    public List<Column> getColumns(ResultSet resultSet) throws SQLException {
        List<Column> columns = new ArrayList<>();
        Column column;
        ResultSetMetaData metaData = resultSet.getMetaData();
        int maxSize = metaData.getColumnCount();
        String columnName;
        int jdbcType;
        String catalogName;
        String tableName;
        String schemaName;
        for (int i = 0; i < maxSize; i++) {
            columnName = metaData.getColumnLabel(i + 1);
            jdbcType = metaData.getColumnType(i + 1);
            catalogName = metaData.getCatalogName(i + 1);
            schemaName = metaData.getSchemaName(i + 1);
            tableName = metaData.getTableName(i + 1);
            column = new Column();
            column.setField(columnName);
            column.setTitle(columnName);
            column.setCatalogName(catalogName);
            column.setTableName(tableName);
            column.setSchemaName(schemaName);
            switch (jdbcType) {
                case Types.SMALLINT:
                case Types.INTEGER:
                case Types.BIGINT:
                case Types.FLOAT:
                case Types.REAL:
                case Types.DOUBLE:
                case Types.BOOLEAN:
                case Types.BIT:
                case Types.DECIMAL:
                case Types.NUMERIC:
                    column.setType(ColumnType.NUMBER.name());
                    break;
                case Types.TIMESTAMP:
                    column.setType(ColumnType.DATE_TIME.name());
                    break;
                case Types.DATE:
                    column.setType(ColumnType.DATE.name());
                    break;
                case Types.BLOB:
                case Types.CLOB:
                case Types.NCLOB:
                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                case Types.LONGNVARCHAR:
                case Types.LONGVARCHAR:
                case Types.SQLXML:
                case Types.CHAR:
                    column.setType(ColumnType.TEXT.name());
                    break;
                default:
                    column.setType(ColumnType.STRING.name());
                    break;
            }
            columns.add(column);
        }
        return columns;
    }

    public List<Map<String, Object>> getData(List<Column> columns, ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> data = new ArrayList<>();
        Map<String, Object> map;
        Map<String, Object> tempMap;
        while (resultSet.next()) {
            map = new HashMap<>();
            tempMap = new HashMap<>();
            for (Column column : columns) {
                Object valueObj = covertValue(column,resultSet.getObject(column.getField()));
                map.put(column.getField(), valueObj);
                if(column.isPrimaryKey()){
                    tempMap.put(column.getField(),valueObj);
                }
            }
            if(tempMap.size()>0){
                map.put("primaryKeyValues",tempMap);
            }
            data.add(map);
        }
        return data;
    }

    public Object covertValue(Column column,Object value){
        if(value==null){
            return null;
        }
        if (column.getType().equals(ColumnType.DATE.name()) && value instanceof Date) {
            return  DATE_FORMAT.format(value);
        }
        if (column.getType().equals(ColumnType.DATE_TIME.name()) && value instanceof LocalDateTime) {
           return  DATE_TIME_FORMAT.format((Date.from(((LocalDateTime) value).atZone(ZoneId.systemDefault()).toInstant())));
        }
        return value;
    }

    public String formatSql(QueryExecuteParams params) {
        String sql = params.getScript();
        if (sql.trim().endsWith(";")) {
            sql = sql.trim().substring(0, sql.lastIndexOf(";"));
        }
        String sqlFormat = "({}) {} {} {}";
        if (StringUtils.isNotBlank(params.getWhereCriteria())) {
            sqlFormat = "{} {} {} {}";
        }
        String where = " where " + params.getWhereCriteria();
        String order = "order by " + params.getOrderCriteria();
        String pageSql = getPageSql(params);
        return Strings.format(sqlFormat, sql, StringUtils.isBlank(params.getWhereCriteria()) ? "" : where, StringUtils.isBlank(params.getOrderCriteria()) ? "" : order,
                pageSql);
    }

    public String getPageSql(QueryExecuteParams params) {
        int limitFrom = (params.getPageIndex() - 1) * params.getPageSize();
        return params.isNoPage() ? "" : " limit " + limitFrom + "," + params.getPageSize();
    }


    public int executeQueryForCount(String sql) throws Exception {
        int rowCount = 0;
        Statement stmt;
        ResultSet rs;
        try {
            sql = "select count(*) as totals from (" + sql + ") tab ";
            stmt = this.connection.createStatement();
            rs = stmt.executeQuery(sql);
            rs.next();
            rowCount = rs.getInt("totals");
        } catch (Exception e) {
            log.error("sql[{}]获取总行数错误：{}", sql, e.getMessage(), e);
            return -1;
        }
        return rowCount;
    }


    public List<PrimaryKey> getPrimaryKeys(String catalog, String tableName) {
        DatabaseMetaData dbMetaData;
        try {
            dbMetaData = getNativeConnection().getMetaData();
        } catch (SQLException e) {
            throw new JdbcMetaAnalysisException(e.getMessage(), e);
        }
        List<PrimaryKey> primaryKeys = Lists.newArrayList();
        try (ResultSet rs = dbMetaData.getPrimaryKeys(catalog, tableName, tableName)) {
            while (rs.next()) {
                primaryKeys.add(new PrimaryKey(rs.getString("COLUMN_NAME"), rs.getShort("KEY_SEQ"), rs.getString("PK_NAME")));
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]表[{}]的主键信息错误", catalog, tableName, e);
        }
        return primaryKeys;
    }


}
